﻿using System;
using System.Collections.Generic;
using System.Data;
using System.Data.OleDb;
using System.Data.SqlClient;
using System.Linq;
using System.Web;
using System.Web.Configuration;

/// <summary>
/// SqlServerConn 的摘要说明
/// </summary>
public class SqlServerConn
{

    // string connstr = ConfigurationManager.ConnectionStrings["mycon"].ConnectionString;
    private static readonly string connstr = WebConfigurationManager.ConnectionStrings["physique_test"].ToString();

    /// <summary>
    /// 输入SQL语句 返回影响行数
    /// </summary>
    /// <param name="strSql"></param>
    /// <returns></returns>
    public int Excu(string strSql)
    {
        int num = -1;
        using (SqlConnection SqlConn = new SqlConnection(connstr))
        {
            SqlCommand SqlComm = new SqlCommand(strSql, SqlConn);

            SqlConn.Open();

            num = SqlComm.ExecuteNonQuery();
        }
        return num;
    }


    /// <summary>   
    /// SqlDataReader转成DataTable实现方法   
    /// </summary>   
    /// <param name="strSql"></param>   
    /// <returns></returns>   
    public DataTable DataReaderToDataTable(string strSql)
    {
        //string connstr = ConfigurationManager.ConnectionStrings["mycon"].ConnectionString;
        SqlConnection SqlConn = new SqlConnection(connstr);
        SqlCommand SqlComm = new SqlCommand(strSql, SqlConn);
        SqlDataReader dataReader = null;
        DataTable dtReturn = null;
        object[] value = null;
        try
        {
            SqlComm.Connection.Open();
            dataReader = SqlComm.ExecuteReader();
            if (dataReader.HasRows)
            {
                dtReturn = CreateTableBySchemaTable(dataReader.GetSchemaTable());
                value = new object[dataReader.FieldCount];
                while (dataReader.Read())
                {
                    dataReader.GetValues(value);
                    dtReturn.LoadDataRow(value, true);
                }
                value = null;
            }

        }
        catch (Exception err)
        { }
        finally
        {
            dataReader.Close();
            SqlComm.Connection.Close();
            SqlComm.Dispose();
        }
        return dtReturn;
    }
    /// <summary>
    /// 创建表结构
    /// </summary>
    /// <param name="pSchemaTable"></param>
    /// <returns></returns>
    protected DataTable CreateTableBySchemaTable(DataTable pSchemaTable)
    {
        DataTable dtReturn = new DataTable();
        DataColumn dc = null;
        DataRow dr = null;
        for (int i = 0; i < pSchemaTable.Rows.Count; i++)
        {
            dr = pSchemaTable.Rows[i];
            dc = new DataColumn(dr["ColumnName"].ToString(), dr["DataType"] as Type);
            dtReturn.Columns.Add(dc);
        }
        dr = null;
        dc = null;
        return dtReturn;
    }


}
